package CinemaDevelop;

import java.sql.*;
import java.time.LocalDateTime;
import java.util.*;

public class CinemaDAO {
    // 用户相关操作
    public static User getUserByUsername(String username) {
        String sql = "SELECT * FROM users WHERE username = ?";
        try (Connection conn = DatabaseConfig.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setString(1, username);
            ResultSet rs = stmt.executeQuery();

            if (rs.next()) {
                String userType = rs.getString("user_type");
                String telephone = rs.getString("telephone");
                String password = rs.getString("password");

                switch (userType) {
                    case "ADMIN":
                        return new Administrator(username, password, telephone);
                    case "MANAGER":
                        return new Manager(username, password, telephone);
                    case "TICKET_SELLER":
                        return new TicketSeller(username, password, telephone);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    // 电影相关操作
    public static List<Movie> getAllMovies() {
        List<Movie> movies = new ArrayList<>();
        String sql = "SELECT * FROM movies";

        try (Connection conn = DatabaseConfig.getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {

            while (rs.next()) {
                movies.add(new Movie(
                        rs.getString("main_actor"),
                        rs.getString("director"),
                        rs.getString("name"),
                        rs.getInt("length"),
                        rs.getDouble("rate")
                ));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return movies;
    }

    // 放映厅相关操作
    public static List<ScreenRoom> getAllScreenRooms() {
        List<ScreenRoom> rooms = new ArrayList<>();
        String sql = "SELECT * FROM screen_rooms";

        try (Connection conn = DatabaseConfig.getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {

            while (rs.next()) {
                ScreenRoom room = new ScreenRoom(rs.getString("type"));
                room.sc = new SeatingChart(
                        rs.getInt("rows_count"),
                        rs.getInt("columns_count")
                );
                rooms.add(room);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return rooms;
    }

    public static void addScreenRoom(ScreenRoom room) {
        String sql = "INSERT INTO screen_rooms (type, rows_count, columns_count) VALUES (?, ?, ?)";

        try (Connection conn = DatabaseConfig.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setString(1, room.type);
            stmt.setInt(2, room.sc.getRow());
            stmt.setInt(3, room.sc.getLine());
            stmt.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // 电影排期相关操作
    public static List<MovieSchedule> getAllMovieSchedules() {
        List<MovieSchedule> schedules = new ArrayList<>();
        String sql = "SELECT ms.schedule_id, ms.show_time, " +
                "m.movie_id, m.name, m.director, m.main_actor, m.length, m.rate, " +
                "sr.room_id, sr.type, sr.rows_count, sr.columns_count " +
                "FROM movie_schedules ms " +
                "JOIN movies m ON ms.movie_id = m.movie_id " +
                "JOIN screen_rooms sr ON ms.room_id = sr.room_id";

        try (Connection conn = DatabaseConfig.getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {

            while (rs.next()) {
                Movie movie = new Movie(
                        rs.getString("main_actor"),
                        rs.getString("director"),
                        rs.getString("name"),
                        rs.getInt("length"),
                        rs.getDouble("rate")
                );

                ScreenRoom room = new ScreenRoom(rs.getString("type"));
                room.sc = new SeatingChart(
                        rs.getInt("rows_count"),
                        rs.getInt("columns_count")
                );

                LocalDateTime time = rs.getTimestamp("show_time").toLocalDateTime();

                MovieSchedule schedule = new MovieSchedule(movie, room, time);
                schedule.setScheduleId(rs.getInt("schedule_id"));
                schedules.add(schedule);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return schedules;
    }

    public static MovieSchedule findSchedule(String movieName, String roomType, LocalDateTime time) {
        String sql = "SELECT ms.schedule_id, ms.show_time, " +
                "m.movie_id, m.name, m.director, m.main_actor, m.length, m.rate, " +
                "sr.room_id, sr.type, sr.rows_count, sr.columns_count " +
                "FROM movie_schedules ms " +
                "JOIN movies m ON ms.movie_id = m.movie_id " +
                "JOIN screen_rooms sr ON ms.room_id = sr.room_id " +
                "WHERE m.name = ? AND sr.type = ? AND ms.show_time = ?";

        try (Connection conn = DatabaseConfig.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setString(1, movieName);
            stmt.setString(2, roomType);
            stmt.setTimestamp(3, Timestamp.valueOf(time));

            ResultSet rs = stmt.executeQuery();

            if (rs.next()) {
                Movie movie = new Movie(
                        rs.getString("main_actor"),
                        rs.getString("director"),
                        rs.getString("name"),
                        rs.getInt("length"),
                        rs.getDouble("rate")
                );

                ScreenRoom room = new ScreenRoom(rs.getString("type"));
                room.sc = new SeatingChart(
                        rs.getInt("rows_count"),
                        rs.getInt("columns_count")
                );

                MovieSchedule schedule = new MovieSchedule(movie, room, rs.getTimestamp("show_time").toLocalDateTime());
                schedule.setScheduleId(rs.getInt("schedule_id"));
                return schedule;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public static void addMovieSchedule(MovieSchedule schedule) {
        String sql = "INSERT INTO movie_schedules (movie_id, room_id, show_time) VALUES (?, ?, ?)";

        try (Connection conn = DatabaseConfig.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {

            int movieId = getOrInsertMovie(conn, schedule.getMovie());
            int roomId = getRoomId(conn, schedule.getRoom().type);

            stmt.setInt(1, movieId);
            stmt.setInt(2, roomId);
            stmt.setTimestamp(3, Timestamp.valueOf(schedule.getTime()));
            stmt.executeUpdate();

            try (ResultSet generatedKeys = stmt.getGeneratedKeys()) {
                if (generatedKeys.next()) {
                    schedule.setScheduleId(generatedKeys.getInt(1));
                }
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void removeMovieSchedule(MovieSchedule schedule) {
        String sql = "DELETE FROM movie_schedules WHERE schedule_id = ?";

        try (Connection conn = DatabaseConfig.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setInt(1, schedule.getScheduleId());
            stmt.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // 座位相关操作
    public static void bookSeat(int scheduleId, int row, int col, String customerInfo) {
        String sql = "INSERT INTO seats (schedule_id, row_num, col_num, status, customer_info) " +
                "VALUES (?, ?, ?, 'OCCUPIED', ?) " +
                "ON DUPLICATE KEY UPDATE status = 'OCCUPIED', customer_info = ?";

        try (Connection conn = DatabaseConfig.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setInt(1, scheduleId);
            stmt.setInt(2, row);
            stmt.setInt(3, col);
            stmt.setString(4, customerInfo);
            stmt.setString(5, customerInfo);
            stmt.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static List<Seat> getSeatsForSchedule(int scheduleId) {
        List<Seat> seats = new ArrayList<>();
        String sql = "SELECT row_num, col_num, status, customer_info FROM seats WHERE schedule_id = ?";

        try (Connection conn = DatabaseConfig.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setInt(1, scheduleId);
            ResultSet rs = stmt.executeQuery();

            while (rs.next()) {
                seats.add(new Seat(
                        rs.getInt("row_num"),
                        rs.getInt("col_num"),
                        rs.getString("status"),
                        rs.getString("customer_info")
                ));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return seats;
    }

    // 辅助方法
    private static int getOrInsertMovie(Connection conn, Movie movie) throws SQLException {
        String sql = "SELECT movie_id FROM movies WHERE name = ?";
        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setString(1, movie.name);
            ResultSet rs = stmt.executeQuery();

            if (rs.next()) {
                return rs.getInt("movie_id");
            }

            String insertSql = "INSERT INTO movies (name, director, main_actor, length, rate) VALUES (?, ?, ?, ?, ?)";
            try (PreparedStatement insertStmt = conn.prepareStatement(insertSql, Statement.RETURN_GENERATED_KEYS)) {
                insertStmt.setString(1, movie.name);
                insertStmt.setString(2, movie.director);
                insertStmt.setString(3, movie.mainActor);
                insertStmt.setInt(4, movie.length);
                insertStmt.setDouble(5, movie.rate);
                insertStmt.executeUpdate();

                try (ResultSet generatedKeys = insertStmt.getGeneratedKeys()) {
                    if (generatedKeys.next()) {
                        return generatedKeys.getInt(1);
                    }
                }
            }
        }
        throw new SQLException("Failed to get or insert movie");
    }

    private static int getRoomId(Connection conn, String roomType) throws SQLException {
        String sql = "SELECT room_id FROM screen_rooms WHERE type = ?";
        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setString(1, roomType);
            ResultSet rs = stmt.executeQuery();

            if (rs.next()) {
                return rs.getInt("room_id");
            }
        }
        throw new SQLException("Room not found: " + roomType);
    }

    public static class Seat {
        private int row;
        private int col;
        private String status;
        private String customerInfo;

        public Seat(int row, int col, String status, String customerInfo) {
            this.row = row;
            this.col = col;
            this.status = status;
            this.customerInfo = customerInfo;
        }

        public int getRow() { return row; }
        public int getCol() { return col; }
        public String getStatus() { return status; }
        public String getCustomerInfo() { return customerInfo; }
    }
}